clear

*Load Data
use "./data_geo.dta", clear

*****************************************************************************
*** Setup: see Kashyap & Stein (2000) and/or Den Haan, Sumner, Yamashiro (2002) for details ***

*Drop non-states 
drop if rssd9200 == "0"
keep if  rssd9210>0 & rssd9210< 57

*Drop Delaware and/or South Dakota (see Jayaratne & Strahan 1998 JLE)
*drop if rssd9210 == 10
*drop if rssd9210 == 46

*Sample period
keep if year_q < tq(1995q1)

*Positive Assets
keep if rcfd2170 > 0

*Commercial Banks Only
keep if rssd9048 == 200 

*Insured
*keep if rssd9424 == 1 | rssd9424 == 2 | rssd9424 == 6
drop if rssd9424 == 0

*Merger
drop if MERGE_CD != .

*Set panel data
xtset rssd9001 year_q

*Fix Total Loans pre-1984 (insert before calcuating loans2 above)
gen loans2 = rcfd1400
replace loans2 = rcfd1400+rcfd2165 if year_q < tq(1984q1)

*Put loans and loan components in real terms
gen c_p_i = cpi/100
gen r_loans = loans2/c_p_i 
gen r_ci = rcfd1600/c_p_i
gen r_rlest = rcfd1410/c_p_i
gen r_con = rcfd1975/c_p_i
gen r_ag = rcfd1590/c_p_i

*Log difference 
gen loans = ln(r_loans) - ln(L.r_loans)
gen ci = ln(r_ci) - ln(L.r_ci) 
gen re = ln(r_rlest) - ln(L.r_rlest) 
gen con = ln(r_con) - ln(L.r_con)
gen ag = ln(r_ag) - ln(L.r_ag) 

*Drop outliers
sort year_q
by year_q: egen sdv = sd(loans)
drop if loans >= 5*sdv
drop if loans <= -5*sdv

*Loan Component Outliers
gen ci_share = rcfd1600/loans2
*drop if ci_share < 0.05
gen re_share = rcfd1410/loans2
*drop if re_share < 0.05
gen con_share = rcfd1975/loans2
*drop if con_share < 0.05
gen ag_share = rcfd1590/loans2
*drop if ag_share < 0.05

*Four Consecutive Quarters of Loan Growth
*ssc install tsspell
tsspell, c(loans > 0)
sort rssd9001
by rssd9001: egen maxrun = max(_seq)
drop if maxrun < 4

*Percentiles
sort year_q
by year_q: egen p95 = pctile(rcfd2170), p(95)
by year_q: egen p99 = pctile(rcfd2170), p(99)

*Generate Quarter/Year/timetrend Series
sort rssd9001 year_q
gen Quarter = quarter(date2)
gen Year = year(date2) 
egen time = group(year_q)

*Choose to keep (or drop) year deregulation was implemented 
replace intra = 1 if intra == .
replace inter =1 if inter == .

* Generate Interaction Terms
gen rr_intra = rr*intra
gen rr_intra1 = L1.rr*intra
gen rr_intra2 = L2.rr*intra
gen rr_intra3 = L3.rr*intra
gen rr_intra4 = L4.rr*intra

gen rr_inter = rr*inter
gen rr_inter1 = L1.rr*inter
gen rr_inter2 = L2.rr*inter
gen rr_inter3 = L3.rr*inter
gen rr_inter4 = L4.rr*inter

*******************************************************************************
*** Additional Controls ***

* State small bank share 
sort rssd9210 year_q  
by rssd9210 year_q: egen ASSETS_s = sum(rcfd2170)

by rssd9210 year_q: egen s50 = pctile(rcfd2170), p(50)
by rssd9210 year_q: egen small_ASSETS_s = sum(rcfd2170) if rcfd2170 < s50
by rssd9210 year_q: gen small_banks2 = small_ASSETS_s/ASSETS_s
by rssd9210 year_q: egen small_banks = max(small_banks2)


* State small bank financial health 
by rssd9210 year_q: egen big_ASSETS_s = sum(rcfd2170) if rcfd2170 >= s50

sort rssd9001 year_q
gen Cap = rcfd3210/rcfd2170

gen s_wt = rcfd2170/small_ASSETS_s
gen b_wt = rcfd2170/big_ASSETS_s

gen wt_cap = s_wt*Cap
replace wt_cap = b_wt*Cap if rcfd2170 >= s50

sort rssd9210 year_q 
by rssd9210 year_q: egen small_health2 = sum(wt_cap) if rcfd2170 < s50
by rssd9210 year_q: egen big_health2 = sum(wt_cap) if rcfd2170 >= s50
by rssd9210 year_q: egen small_health = max(small_health2)
by rssd9210 year_q: egen big_health = max(big_health2)
by rssd9210 year_q: gen state_health = small_health - big_health

sort rssd9001 year_q

gen rr_bank = rr*small_banks
gen rr_bank1 = L1.rr*small_banks
gen rr_bank2 = L2.rr*small_banks
gen rr_bank3 = L3.rr*small_banks
gen rr_bank4 = L4.rr*small_banks

gen rr_health = rr*state_health
gen rr_health1 = L1.rr*state_health
gen rr_health2 = L2.rr*state_health
gen rr_health3 = L3.rr*state_health
gen rr_health4 = L4.rr*state_health

gen rr_bus = rr*small_bus
gen rr_bus1 = L1.rr*small_bus
gen rr_bus2 = L2.rr*small_bus
gen rr_bus3 = L3.rr*small_bus
gen rr_bus4 = L4.rr*small_bus

*******************************************************************************
*** Table A4 ***

xtreg loans L(1/4).loans L(0/4).pi_pchg L(0/4).hpi_chg intra inter rr_intra rr_intra1 rr_intra2 rr_intra3 rr_intra4 rr_inter rr_inter1 rr_inter2 rr_inter3 rr_inter4 i.year_q i.rssd9210#c.time, fe vce(cluster rssd9210) nonest
lincom rr_inter + rr_inter1 + rr_inter2 + rr_inter3 + rr_inter4

xtreg loans L(1/4).loans L(0/4).pi_pchg L(0/4).hpi_chg small_banks inter rr_bank rr_bank1 rr_bank2 rr_bank3 rr_bank4 rr_inter rr_inter1 rr_inter2 rr_inter3 rr_inter4 i.year_q, fe vce(cluster rssd9210) nonest
lincom rr_inter + rr_inter1 + rr_inter2 + rr_inter3 + rr_inter4
lincom rr_bank + rr_bank1 + rr_bank2 + rr_bank3 + rr_bank4

xtreg loans L(1/4).loans L(0/4).pi_pchg L(0/4).hpi_chg state_health inter rr_health rr_health1 rr_health2 rr_health3 rr_health4 rr_inter rr_inter1 rr_inter2 rr_inter3 rr_inter4 i.year_q, fe vce(cluster rssd9210) nonest
lincom rr_inter + rr_inter1 + rr_inter2 + rr_inter3 + rr_inter4
lincom rr_health + rr_health1 + rr_health2 + rr_health3 + rr_health4

xtreg loans L(1/4).loans L(0/4).pi_pchg L(0/4).hpi_chg small_bus inter rr_bus rr_bus1 rr_bus2 rr_bus3 rr_bus4 rr_inter rr_inter1 rr_inter2 rr_inter3 rr_inter4 i.year_q, fe vce(cluster rssd9210) nonest
lincom rr_inter + rr_inter1 + rr_inter2 + rr_inter3 + rr_inter4
lincom rr_bus + rr_bus1 + rr_bus2 + rr_bus3 + rr_bus4
*******************************************************************************